from io import BytesIO
from urllib.parse import quote

from django.core.paginator import Paginator
from django.db.models import Q
from django.db.transaction import get_connection
from django.http import JsonResponse, HttpResponse
from django.shortcuts import render
from django.utils import timezone
from openpyxl import Workbook

from search.models import Record


def show_index(request):
    return render(request, 'index.html', {})


def show_charts(request):
    return render(request, 'chart.html')


def get_bar_data(request):
    years, totals = [], []
    conn = get_connection()
    with conn.cursor() as cursor:
        cursor.execute('select year(makedate) as year, count(no) as total '
                       ' from tb_record group by year order by year')
        for row in cursor.fetchall():
            years.append(row[0])
            totals.append(row[1])
        dealts = [0] * len(years)
        cursor.execute('select year(makedate) as year, count(no) as total '
                       ' from tb_record where dealt=1 '
                       ' group by year order by year')
        index = 0
        for row in cursor.fetchall():
            while row[0] != years[index]:
                index += 1
            dealts[index] = row[1]
            index += 1
    return JsonResponse({'years': years, 'totals': totals, 'dealts': dealts})


def export_excel(request):
    carinfo = request.GET.get('carinfo', '')
    queryset = Record.objects.filter(is_deleted=False) \
        .defer('deleted_time', 'is_deleted', 'updated_time') \
        .select_related('car').order_by('-makedate')
    if carinfo:
        queryset = queryset.filter(
            Q(car__carno__iexact=carinfo) | Q(car__owner__contains=carinfo)
        )
    wb = Workbook()
    sheet = wb.active
    col_names = ('车牌号', '车主姓名', '违章原因', '违章时间', '处罚方式', '是否受理')
    for index, col in enumerate('ABCDEF'):
        sheet[f'{col}1'] = col_names[index]
    row = 2
    for record in queryset:
        sheet[f'A{row}'] = record.car.carno
        sheet[f'B{row}'] = record.car.owner
        sheet[f'C{row}'] = record.reason
        sheet[f'D{row}'] = record.makedate
        sheet[f'E{row}'] = record.punish
        sheet[f'F{row}'] = '已受理' if record.dealt else '未受理'
        row += 1
    buffer = BytesIO()
    wb.save(buffer)
    resp = HttpResponse(buffer.getvalue())
    # 设置响应头指定MIME类型（告诉浏览器服务器返回的内容类型）
    resp['content-type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    filename = quote('违章记录表.xlsx')
    # 设置响应头指定内容处置方式（inline-内联打开；attachment-下载）
    # 如果要设置中文文件名，需要将中文处理成百分号编码，否则不能写到响应头
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp


def search_records(request):
    curr_page = int(request.GET.get('currentPage', '1'))
    page_size = int(request.GET.get('pageSize', '5'))
    carinfo = request.POST.get('carinfo', '')
    # 多对一或一对一关联时可以使用select_related方法解决"1+N查询问题"
    # 多对多关联可以使用prefetch_related方法解决"1+N查询问题"
    # 可以使用QuerySet对象的order_by方法对查询结果按照哪个字段进行排序
    queryset = Record.objects.filter(is_deleted=False)\
        .defer('deleted_time', 'is_deleted', 'updated_time')\
        .select_related('car').order_by('-makedate')
    if carinfo:
        queryset = queryset.filter(
            Q(car__carno__iexact=carinfo) | Q(car__owner__contains=carinfo)
        )
    # 创建分页器对象（第一个参数是分页之前的QuerySet对象，第二个参数是页面大小）
    # Paginator对象的get_page方法可以指定页码获取对应的页（Page）对象
    # Paginator对象的count属性可以获得总共有多少条数据（可以换算成总页数）
    paginator = Paginator(queryset, page_size)
    # Page对象的object_list属性代表当前页的数据列表
    # Page对象的number属性代表当前页的页码
    # Page对象的has_previous和has_next方法可以返回布尔值表示有没有上一页/下一页
    page = paginator.get_page(curr_page)
    return render(request, 'index.html', {
        'records': page.object_list,
        'current_page': page.number,
        'total_page': (paginator.count - 1) // page_size + 1,
        'has_prev': page.has_previous(),
        'has_next': page.has_next(),
        'prev_page': page.number - 1,
        'next_page': page.number + 1,
        'carinfo': carinfo
    })


def handle_record(request):
    rno = int(request.GET.get('rno', '0'))
    result = {'code': 10001, 'msg': '操作失败'}
    if rno:
        record = Record.objects.filter(no=rno).first()
        if record and not record.dealt:
            record.dealt = True
            record.save()
            result = {'code': 10000, 'msg': '操作成功'}
    return JsonResponse(result)


def delete_record(request):
    rno = int(request.GET.get('rno', '0'))
    result = {'code': 10001, 'msg': '操作失败'}
    if rno:
        record = Record.objects.filter(no=rno).first()
        if record and record.dealt:
            record.is_deleted = True
            record.deleted_time = timezone.now()
            record.save()
            result = {'code': 10000, 'msg': '操作成功'}
    return JsonResponse(result)
